﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web.UI;
using MyDAL;

namespace MyClass.PagerHelper
{
    /// <summary>
    /// Access分页
    /// </summary>
    public class AccessPagerHelper
    {

        #region 私有变量
        /*分页存储过程
         * set statistics time on --耗时
         Create   Procedure [dbo].[prGetRecordByPage]
        (
        @pagesize int, --每页的记录条数
        @pagenum int, --当前页面  1为第一页
        @QuerySql varchar(1000),--部分查询字符串,如 '  FROM t_Company where...'
        @keyId varchar(500), --列ID
        @order varchar(5),--desc/asc
        @clos varchar(50)--orderseq,companycode... 
        )
        AS
        Begin 
        Declare @SqlText AS Varchar(1000)
        declare @SqlText1 AS Varchar(1000)
        set @SqlText=' SELECT * '
        +' FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@keyId+' '+@order+', '+@keyId+') AS rownum,'+@clos +' '+ @QuerySql+' ) AS D '  
        +' WHERE rownum BETWEEN '+Cast((@pagenum-1)*(@pagesize+1) as varchar(10))+' AND '+Cast(@pagenum*@pagesize as varchar(10))+ ''
        Exec(@SqlText)
        set @SqlText1=' select count(*) as totalCount '+@QuerySql
        Exec(@SqlText1)
        end
        */
        private  int _totalCount;
        private  string _DbUrl;
        private  DataTable _dt;
        private int _cur=1;
        private int _size=0;
        private string _clos;
        private string _tableId;
        private string _tableName;
        private string _condition;
        private string _order;
        private string _conn;
        private IDataBase idb;
        #endregion

        #region 属性
        /// <summary>
        /// 当前页
        /// </summary>
        public int Cur
        {
            get { return _cur; }
            set { _cur = value; }
        }

        /// <summary>
        /// 页大小
        /// </summary>
        public int Size
        {
            get { return _size; }
        }
        /// <summary>
        /// 数据内容
        /// </summary>
        public  DataTable Dt
        {
            get { return _dt; }
        }
        /// <summary>
        /// 总条数
        /// </summary>
        public  int TotalCount
        {
            get { return _totalCount; }
        }
     
        #endregion

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="cur">当前页(1为第一页)</param>
        /// <param name="clos">显示的列clo1,clo2.. 或.*</param>
        /// <param name="tableId">表ID</param>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件[ [clos1=value]|Empty]</param>
        /// <param name="size">页大小</param>
        /// <param name="orderParam">排序 [order by [clos] desc/asc] | Empty</param>
        public AccessPagerHelper( string clos, string tableId, string tableName, string condition, int size, string orderParam,string conn,MyDAL.Enumeration.ConnStringType type)
        {
            idb = new OleDbDataBase(conn, type);
            this._clos = clos;
            this._tableId = tableId;
            this._tableName = tableName;
            this._condition = condition;
            this._size = size;
            this._order = orderParam;
            this._conn = conn;
        }

  
        /// <summary>
        /// 非sql2005以上版本使用
        /// </summary>
        /// <param name="page">当前页面</param>
        /// <param name="cur">当前页</param>
        /// <returns>当前页内容</returns>
        public  DataSet GetCurrentPageData(int cur)
        {
            DataSet[] dss = new DataSet[2];
            this._cur = cur;
            string[] sql = GetSQLCommandString(cur);
            DataSet ds = idb.GetDataSet(sql[0]);
            DataSet ds1 = idb.GetDataSet(sql[1]);
            _totalCount = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
            _dt = ds.Tables[0];
            return ds;
        }

        /// <summary>
        /// 非sql2005以上版本使用
        /// </summary>
        /// <param name="page">当前页面</param>
        /// <param name="cur">当前页</param>
        /// <param name="totalCount">发返总页数</param>
        /// <returns>当前页内容</returns>
        public DataSet GetCurrentPageData(int cur,out int totalCount)
        {

            DataSet[] dss = new DataSet[2];
            this._cur = cur;
            string[] sql = GetSQLCommandString(cur);
            DataSet ds = idb.GetDataSet(sql[0]);
            DataSet ds1 = idb.GetDataSet(sql[1]); ;
            _totalCount = Convert.ToInt32(ds1.Tables[0].Rows[0][0]);
            totalCount = _totalCount;
            _dt = ds.Tables[0];
            return ds;
        }

        /// <summary>
        /// 返回SQL分页字符窜
        /// </summary>
        /// <returns>返回字符窜数组string[0]为查询数据，string[1]为查询总条数</returns>
        public string[] GetSQLCommandString(int cur)
        {

            this._cur = cur;
            string[] s = new string[2];

            string sql = string.Empty;
            string sql1 = string.Empty;
            if (string .IsNullOrEmpty(this._condition))
            {
                if (this._cur == 1)
                {
                    sql = string.Format("select top {0} {1} from {2}  order by {3} asc", this._size, this._clos, this._tableName, this._tableId);
                }
                else
                {
                    sql = string.Format("select top {0} {1} from {2} where {3} > ( select max({3}) from  ( select top {7} {1} from {2} order by {3} asc) as t )   {6} ",
                      this._size * this._cur, this._clos, this._tableName, this._tableId, this._cur, this._condition, this._order, this._size * (this._cur - 1));
                }
                sql1 = string.Format("select count(*) from {0}", this._tableName);
            }
            else
            {
                if (this._cur == 1)
                {
                    sql = string.Format("select top {0} {1} from {2} where {4} order by {3} asc", this._size, this._clos, this._tableName, this._tableId, this._condition);
                }
                else
                {
                    sql = string.Format("select top {0} {1} from {2} where {3} > ( select max({3}) from  ( select top {7} {1} from {2} order by {3} asc) as t ) and  {5} {6} ",
                       this._size * this._cur, this._clos, this._tableName, this._tableId, this._cur, this._condition, this._order, this._size * (this._cur - 1));
                }
                sql1 = string.Format("select count(*) from {0} where {1} ", this._tableName, this._condition);
            }

            s[0] = sql;
            s[1] = sql1;
            return s;
        }

    

    }
}

